<?php
class Mkpi extends CI_Model {

	/**
	 * Mkpi::Mkpi()
	 * 
	 * @return
	 */
	function Mkpi(){
		parent::__construct();
	}
	
        /**
         * Mkpi::getKPI()
         * 
         * @return
         */
        function getKPI($id = "")
        {
                #id isinya a/ user_id, jadi harus cek dulu jenis group usernya apa?
                $dblokal  = $this->load->database("default", TRUE);
                $userlogin = $id;
                
                $posisi = $this->getPosisi($id);
                $query = "";
                if($posisi == "Agent" || $posisi =="Outlet"){
                        $query = $dblokal->query("SELECT t_trx_kpi_achievement.*, kpi_description
                                                 FROM t_trx_kpi_achievement, t_mtr_kpi
                                        WHERE t_trx_kpi_achievement.kpi_code = t_mtr_kpi.kpi_code
                                                AND user_id = '". $userlogin ."'");
                }elseif($posisi == "Cluster" || $posisi == "Regional"){
                        #get sales di bawahnya
                        /*$query = $dblokal->query("SELECT t_trx_kpi_achievement.*, kpi_description
                                                 FROM t_trx_kpi_achievement, t_mtr_kpi
                                                WHERE t_trx_kpi_achievement.kpi_code = t_mtr_kpi.kpi_code
                                                        AND user_id IN (
                                                                SELECT user_id FROM t_mtr_territory WHERE parent_id= f_get_cluster('$userlogin')
                                                        )");*/
                        $query = $dblokal->query("SELECT *
                                                 FROM t_mtr_user
                                                WHERE user_id IN (
                                                                SELECT user_id FROM t_mtr_territory WHERE parent_id= f_get_cluster('$userlogin')
                                                        )");
                }elseif($posisi == "Regionala"){
                        $query = $dblokal->query("SELECT t_trx_kpi_achievement.*, kpi_description
                                                 FROM t_trx_kpi_achievement, t_mtr_kpi
                                                WHERE t_trx_kpi_achievement.kpi_code = t_mtr_kpi.kpi_code
                                                        AND user_id IN (
                                                                SELECT a.user_id FROM t_mtr_territory a, t_mtr_territory b
                                                                WHERE b.territory_id = a.parent_id AND 
                                                                b.parent_id= f_get_cluster('$userlogin')
                                                        )");
                }
                #return $posisi;
                if( $query->num_rows() > 0 ) {
                        return $query->result();
                } else {
                        return array();
                }
	}
        
        /**
         * Mkpi::getPosisi()
         * 
         * @return
         */
        function getPosisi($id){
                $dblokal  = $this->load->database("default", TRUE);
                
                $dblokal->select('territory_type_name');
                $dblokal->from("t_mtr_territory");
                $dblokal->join("t_mtr_territory_type", "t_mtr_territory_type.territory_type_id = t_mtr_territory.territory_type_id ");
                $dblokal->where(array("user_id"=>$id));
                $query = $dblokal->get();
                
                if( $query->num_rows() > 0 ) {
                        $h = $query->row();
                        return $h->territory_type_name;
                } else {
                        return array();
                }
        }
        
        /**
         * Mkpi::getJudul()
         * 
         * @return
         */
        function getJudul($id){
                
                $userlogin = $this->session->userdata('username');
                                
                $posisi  = $this->getPosisi($userlogin);
                $posisi_id  = $this->getPosisi($id);
                $judul = "";
                if($posisi == "Regional"){
                        $judul = "<a href='". base_url() ."index.php/mykpi'>".$this->getPosisi($userlogin)."</a>";
                        if($id <> $userlogin){
                                if($posisi_id == "Cluster"){
                                        $judul .= " >> <a href='". base_url() ."index.php/mykpi'>". $this->getPosisi($id)."</a>";
                                }elseif($posisi_id == "Agent" || $posisi_id == "Outlet"){
                                        $ter_id_cluster = $this->mglobal->showdata("parent_id","t_mtr_territory",array('user_id'=>$id),"dblokal");
                                        $user_id_cluster = $this->mglobal->showdata("user_id","t_mtr_territory",array('territory_id'=>$ter_id_cluster),"dblokal");
                                        
                                        $judul .= " >> <a href='". base_url() ."index.php/mykpi'>". $this->getPosisi($user_id_cluster)."</a>";
                                        $judul .= " >> <a href='". base_url() ."index.php/mykpi'>". $this->getPosisi($id)."</a>";
                                }
                        }
                }elseif($posisi == "Cluster"){
                        $judul = "<a href='". base_url() ."index.php/mykpi'>".$this->getPosisi($userlogin)."</a>";
                        if($id <> $userlogin){
                                $judul .= " >> <a href='". base_url() ."index.php/mykpi'>". $this->getPosisi($id)."</a>";
                        }
                }elseif($posisi == "Agent" || $posisi == "Outlet"){
                        $judul = $this->getPosisi($userlogin);
                }
                return $judul;
        }

        /**
         * Mkpi::getTarget()
         * @author  Rizki
         * @param   none
         * @return  array $result
         * 
         * get list of all Target Assignment that authorized to the login user
         */
        function getTarget() {            
            $dblokal  = $this->load->database("default", TRUE);
            $query = $dblokal->query(" SELECT 
                                        tar.target_id, tar.nik, usr.user_name, substr(tar.kpi_month,1,4) as year,substr(tar.kpi_month,5) as month, tar.kpi_target, tar.kpi_code
                                        FROM 
                                        t_trx_target_monthly tar
                                        LEFT JOIN t_mtr_user usr
                                        ON usr.nik=tar.nik  
                                        WHERE usr.reporting_to='".$this->session->userdata('username')."' AND tar.istatus=1"  
                                      );  
            return $query->result(); 
        }
		
		function getUnapproved() {            
            $dblokal  = $this->load->database("default", TRUE);
            $query = $dblokal->query(" SELECT 
                                        tar.target_id, tar.nik, usr.user_name, substr(tar.kpi_month,1,4) as year,substr(tar.kpi_month,5) as month, tar.kpi_target, tar.kpi_code
                                        FROM 
                                        t_trx_target_monthly tar
                                        LEFT JOIN t_mtr_user usr
                                        ON usr.nik=tar.nik  
                                        WHERE tar.istatus=0"  
                                      );  
            return $query->result(); 
        }
        
		function updateApproval($id,$status){
			$dblokal=$this->load->database("default", TRUE);
			$query=$dblokal->query("UPDATE t_trx_target_monthly SET istatus=".$status." WHERE target_id=".$id);
			$query->result();
		}
		
        /**
         * Mkpi::deleteTarget()
         * @author  Rizki
         * @param   integer $target_id
         * @return  none
         * 
         * Delete Target Assignment
         */
        function deleteTarget($target_id) {
            $dblokal=$this->load->database("default",true);
            $this->db->update('t_trx_target_monthly', array("istatus"=>"-5"),array( "target_id "=>$target_id));
        }
        
        /**
         * Mkpi::deleteKPI()
         * @author  Rizki
         * @param   string $kpi_code
         * @return  none
         * 
         * Delete KPI 
         */
        function deleteKPI($id) {
            $dblokal=$this->load->database("default",true);
            $this->db->update('t_mtr_kpi', array("istatus"=>"-5"),array( "kpi_code "=>$id));
        }
        
		function getAllocatedKPI($id){
			$dblokal=$this->load->database("default",true);
			$query=$dblokal->query("SELECT (target-allocated) as unallocated from t_trx_target_allocation where kpi_code=(SELECT kpi_parent from t_mtr_kpi where kpi_code=(SELECT kpi_code from t_trx_target_monthly where target_id='" . $id. "')) ");
			//print_r($query->result_array());
			foreach($query->result_array() as $key=>$value){
				return $value['unallocated'];
			}
			return 0;
		}
		
        /**
         * Mkpi::getKPIByUserGroup()
         * @author  Rizki
         * @param   integer $user_group_id
         * @return  array $result
         * 
         * Get KPI list by user_group_id
         */
        function getKPIByUserGroup($usr_group,$month,$year) {
            $dblokal=$this->load->database("default",true);
            if ($usr_group=="") {
                $query=$dblokal->query("SELECT kpi.kpi_code,kpi.kpi_description, kpi.remark,grp.user_group_name, kpi.kpi_parent					
				
                                    FROM t_mtr_kpi kpi
                                    INNER JOIN t_mtr_user_group grp
                                    ON kpi.user_group_id=grp.user_group_id
                                    WHERE kpi_parent ='' and kpi.istatus=1 order by kpi_code");
            } else {              
				$y=date('Y');
				$m=date('m');
				if($m==12){
						$m="01";
						$y=$y+1;
					} else {
						$m=$m+1;
						if($m<10)
							$m="0".$m;
					}
                $query=$dblokal->query("SELECT kpi.kpi_code, kpi.kpi_description, kpi.remark,  kpi.kpi_parent,
								(
								SELECT (kpi2.target - kpi2.allocated) from t_trx_target_allocation kpi2
								where kpi2.kpi_code=kpi.kpi_parent and nik='".$this->session->userdata('username')."' and allocated_month='".$year.$month."'
								) as unallocated_target
                                FROM t_mtr_kpi kpi
                                INNER JOIN t_mtr_user_group grp
                                ON kpi.user_group_id=grp.user_group_id
                                where grp.user_group_id=".$usr_group
                                );
            }               
            return $query->result();            
        }
		
        /**
         * Mkpi::getAllUserGroup()
         * @author  Rizki
         * @param   none
         * @return  array $result
         * 
         * Get All User Group list
         */
		 
		 
        function getAllUserGroup(){
            $dblokal=$this->load->database("default",true);
            $query=$dblokal->query("SELECT usr.user_group_id, usr.user_group_name
                                FROM t_mtr_user_group usr
                                where usr.istatus=1 order by user_group_id"
                            );
			$data=$query->result_array();
            return $data;  
        }
        
		function getTerritoryName($type_id="",$keyword="",$parent_id=""){
			$dblokal=$this->load->database("default",true);
			$query=$dblokal->query("SELECT territory_id,territory_name,u.user_name, u.nik from t_mtr_territory  LEFT OUTER JOIN t_mtr_user u on t_mtr_territory.user_id = u.user_id  where territory_type_id=".$type_id." and lower(territory_name) like lower('%".$keyword."%') and parent_id=".$parent_id);
			
			return $query->result();
		}
		
        /**
         * Mkpi::getKPIByKPICode()
         * @author  Rizki
         * @param   string $kpi_code
         * @return  array $result
         * 
         * Get KPI list by Code
         */
        function getKPIByKPICode($kpi_code) {
            $dblokal=$this->load->database("default",true);
            $query=$dblokal->query("SELECT kpi.kpi_code, kpi.kpi_description, kpi.remark, kpi.user_group_id
                                FROM t_mtr_kpi kpi
                                where kpi.kpi_code='".$kpi_code."'"
                            );
            foreach($query->row() as $key=>$value) {
                $data[$key]=$value;
            }
            return $data;            
        }
                
        /**
         * Mkpi::setTarget()
         * @author  Rizki
         * @param   array $data   - array of new target assignment
         * @return  Boolean
         *          0 - Failed
         *          1 - Succeed
         * 
         * Set new target assignment
         */
        function setTarget($data) {
            $dblokal=$this->load->database("default",true);
            $query=$dblokal->query("SELECT * from t_trx_target_monthly where nik='".$data[0]['nik']."' and kpi_month='".$data[0]['kpi_month']."'");
            if($query->num_rows()==0) {  
                $dblokal->insert_batch('t_trx_target_monthly',$data);
				foreach($data as $key=>$value){
					$query3=$this->db->query("SELECT kpi_parent from t_mtr_kpi where kpi_code='".$value['kpi_code']."'");
					
						foreach ($query3->row() as $key=>$values)
						{
							$kpi_parent=$values;
						}
					$query2=$dblokal->query("SELECT * from t_trx_target_allocation where nik='".$this->session->userdata('username')."' and allocated_month='".$data[0]['kpi_month']."' and kpi_code='".$kpi_parent."'");
					if($query2->num_rows()==0){
						$dt['kpi_code']=$kpi_parent;
						$dt['nik']=$this->session->userdata('username');
						$dt['target']=0;
						$dt['allocated']=$value['kpi_target'];
						$dt['allocated_month']=$data[0]['kpi_month'];
						$dt['created_by']=$this->session->userdata('username');
						$dblokal->insert('t_trx_target_allocation',$dt);
					} else {
						$query3=$dblokal->query("UPDATE t_trx_target_allocation set allocated=allocated+".$value['kpi_target']." where kpi_code='".$kpi_parent."' and nik='".$this->session->userdata('username')."' and allocated_month='".$data[0]['kpi_month']."'");
					}					
				}
                return 1;
            } else {
                return 0;
            }
        }
		
		function setAllocation($data){
			$dblokal=$this->load->database("default",true);
            $query=$dblokal->query("SELECT * from t_trx_target_allocation where nik='".$data[0]['nik']."' and allocated_month='".$data[0]['allocated_month']."'");
            if($query->num_rows()==0) {  
                $dblokal->insert_batch('t_trx_target_allocation',$data);
			}
		}
        
		function getDefaultPeriod(){			
            $dblokal=$this->load->database("default",true);
			$query=$dblokal->query("select lookup_value from t_mtr_lookup where lookup_key = 'kpi_target_period' limit 1;");
			//print_r ($query->result_array());
			foreach($query->result_array() as $key =>$value){
				return $value['lookup_value'];
			}
		}
		
		
        /**
         * Mkpi::setKpi()
         * @author  Rizki
         * @param   array $data - array of new KPI
         * @return  Boolean
         *          0 - Failed
         *          1 - Succeed
         * 
         * Set new kpi
         */
        function setKpi($data){
            $dblokal=$this->load->database("default",true);
            $query=$dblokal->query("SELECT * from t_mtr_kpi where kpi_code='".$data['kpi_code']."'");
            if($query->num_rows()==0) {
                $dblokal->insert('t_mtr_kpi',$data);
                return 1;
            } else {
                return 0;
            }
            
        }
        
        /**
         * Mkpi::getTargetById()
         * @author  Rizki
         * @param   integer $target_id
         * @return  array $result
         * 
         * Get target assignment by target_id
         */
        function getTargetById($target_id){
            $dblokal=$this->load->database("default",true);           
            $query= $dblokal->query("SELECT tar.target_id,
                                        tar.nik, usr.user_name, tar.kpi_code, tar.kpi_target, kpi.kpi_description
                                        FROM
                                        t_trx_target_monthly tar
                                        INNER JOIN t_mtr_kpi kpi
                                        ON kpi.kpi_code=tar.kpi_code
                                        INNER JOIN t_mtr_user usr
                                        ON usr.nik=tar.nik
                                        where tar.target_id=".$target_id." and tar.istatus=1 limit 1"
                                    );
            return $query->result();
        }
        
		  function getAllocationById($allocation_id){
            $dblokal=$this->load->database("default",true);           
            $query= $dblokal->query("SELECT allo.allocation_id,allo.nik, usr.user_name, 
										allo.kpi_code, kpi.kpi_description, allo.target, allo.allocated_month
                                        FROM
                                        t_trx_target_allocation allo
                                        INNER JOIN t_mtr_kpi kpi
                                        ON kpi.kpi_code=allo.kpi_code
                                        INNER JOIN t_mtr_user usr
                                        ON usr.nik=allo.nik
                                        where allo.allocation_id=".$allocation_id." and allo.allocated_month limit 1"
                                    );
            return $query->result();
        }		
		
        /**
         * Mkpi::updateTarget()
         * @author  Rizki
         * @param   integer $target_id
         * @param   array $newdata - new target assignment data to be updated         
         * @return  none
         * 
         * Update target assignment by $target_id as PK
         */
        function updateTarget($target_id,$newdata){
            $dblokal=$this->load->database("default",true);
            $dblokal->update('t_trx_target_monthly', $newdata, array('target_id'=>$target_id));         
        }
		
		function updateAllocation($allocation_id,$newdata){
            $dblokal=$this->load->database("default",true);
            $dblokal->update('t_trx_target_allocation', $newdata, array('allocation_id'=>$allocation_id));         
        }
        
         /**
          * Mkpi::updateKPI()
          * @author Rizki
          * @param  string $kpi_code
          * @param  array $newdata - new kpi data to be updated
          * @return none
          * 
          * Update kpi by $kpi_code as PK
          */
         function updateKPI($kpi_code,$newdata){
            $dblokal=$this->load->database("default",true);
            $dblokal->update('t_mtr_kpi', $newdata, array('kpi_code'=>$kpi_code));           
        }
        
        /**
         * Mkpi::getCluster()
         * @author  Rizki
         * @param   none
         * @return  integer @territory_id
         * 
         * Resolve territory_id from login user
         */
        function getCluster(){
            $dblokal=$this->load->database("default",true);
            $query=$dblokal->query("SELECT f_get_cluster('".$this->session->userdata('username')."') as territory_id from t_mtr_user LIMIT 1");
            foreach ($query->result() as $row){
                return $row->territory_id;
            }
        }
        
        /**
         * Mkpi::getDownline()
         * @author  Rizki
         * @param   string $keyword
         * @return  array $result
         * 
         * Get Downline by keyword (this function is used in autocomplete textbox)
         */
        function getDownline($keyword=""){
            $dblokal=$this->load->database("default", TRUE);
            $query=$dblokal->query("SELECT nik,user_id, user_name, user_group_id
                                        from t_mtr_user where reporting_to='".$this->session->userdata('username')."'
                                        AND nik like '".$keyword."%' and istatus=1
                                    ");
            return $query->result();
        }
		
		function getAllocatedTarget($keyword=""){
			$dblokal=$this->load->database("default",TRUE);
			            $query = $dblokal->query(" SELECT 
                                        alc.allocation_id, alc.nik, usr.user_name, alc.kpi_code, substr(alc.allocated_month,1,4) as year,substr(alc.allocated_month,5) as month, alc.target, alc.allocated
                                       , kpi.kpi_description
										FROM 
                                        t_trx_target_allocation alc
                                        LEFT JOIN t_mtr_user usr
                                        ON usr.nik=alc.nik  
										LEFT JOIN t_mtr_kpi kpi
										ON alc.kpi_code=kpi.kpi_code
                                        "  
                                      ); 
			return $query->result();
		}
		
		function getAllClusterCoordinator($keyword=""){
            $dblokal=$this->load->database("default", TRUE);
            $query=$dblokal->query("SELECT nik,user_id, user_name, user_group_id
                                        from t_mtr_user where nik like '".$keyword."%' and istatus=1 and user_group_id=4
                                    ");
            return $query->result();
        }
		
}
            
            /**
 * "SELECT
 *                                         DISTINCT 
 *                                             ter.user_id, usr.nik, usr.user_name, usr.user_group_id
 *                                         FROM 
 *                                             t_mtr_territory ter                                        
 *                                         JOIN 
 *                                             t_mtr_user usr on ter.user_id = usr.user_id
 *                                             AND ter.parent_id = f_get_cluster('".$this->session->userdata('username')."')
 *                                         WHERE 
 *                                             usr.nik LIKE '".$keyword."%'
 *                                     UNION
 *                                       SELECT 
 *                                         DISTINCT 
 *                                             ut.user_id, usr.nik, usr.user_name, usr.user_group_id                                       
 *                                         FROM 
 *                                             t_mtr_user_territory ut                                        
 *                                         JOIN 
 *                                             t_mtr_user usr on ut.user_id = usr.user_id
 *                                         AND
 *                                             usr.reporting_to = '".$this->session->userdata('username')."'
 *                                        WHERE 
 *                                        usr.nik LIKE '".$keyword."%'
 *                                         ORDER BY user_name"
);
 */            
 
 
 